package org.opens.util;

import lombok.extern.slf4j.Slf4j;
import org.apache.poi.hssf.usermodel.HSSFWorkbook;
import org.apache.poi.ss.usermodel.*;
import org.apache.poi.ss.util.CellRangeAddress;

import java.io.File;
import java.io.FileInputStream;
import java.io.FileOutputStream;
import java.io.IOException;
import java.util.ArrayList;
import java.util.HashMap;
import java.util.List;
import java.util.Map;

/**
 * 功能描述
 *
 * @author: zhangyuming
 * @date: 2024/3/28 16:12
 */
@Slf4j
public class ExcelSplitUtil {

    private static final int HEADER_ROWS = 3; // Number of rows to be treated as common header

    public static List<String> splitExcel(String filePath, int maxRows, String descPath) throws IOException {
        List<String> partFilePathList = new ArrayList<>();

        FileInputStream inputStream = null;
        Workbook workbook = null;

        try {
            inputStream = new FileInputStream(new File(filePath));
            workbook = new HSSFWorkbook(inputStream);

            Sheet sheet = workbook.getSheetAt(0); // Get the first sheet
            int rowCount = sheet.getLastRowNum() + 1;

            if (rowCount > HEADER_ROWS + maxRows) {
                int partCount = (int) Math.ceil((double) rowCount / maxRows);
                for (int partIndex = 0; partIndex < partCount; partIndex++) {
                    int startRow = partIndex * maxRows;
                    int endRow = Math.min((partIndex + 1) * maxRows, rowCount);
                    Workbook currentWorkbook = new HSSFWorkbook();
                    Sheet currentSheet = currentWorkbook.createSheet(sheet.getSheetName());
                    copyCommonHeader(sheet, currentSheet); // Copy common header
                    copyMergedRegions(sheet, currentSheet); // Copy merged regions
                    copyRows(sheet, currentSheet, startRow + HEADER_ROWS, endRow); // Copy data rows
                    freezeHeaderRows(currentSheet, HEADER_ROWS); // Freeze header rows
                    String partFilePath = saveWorkbook(currentWorkbook, filePath, partIndex, descPath);
                    partFilePathList.add(partFilePath);
                }
            } else {
                String partFilePath = saveWorkbook(workbook, filePath, 0, descPath);
                partFilePathList.add(partFilePath);
            }
            return partFilePathList;
        } catch (Exception e) {
            throw new RuntimeException(e);
        } finally {
            if (workbook != null) {
                try {
                    workbook.close();
                } catch (IOException e) {
                    // pass
                }
            }
            if (inputStream != null) {
                try {
                    inputStream.close();
                } catch (IOException e) {
                    // pass
                }
            }
        }
    }

    private static void copyCommonHeader(Sheet sourceSheet, Sheet targetSheet) {
        Map<CellStyle, CellStyle> styleMap = new HashMap<>();
        for (int i = 0; i < HEADER_ROWS; i++) {
            Row sourceRow = sourceSheet.getRow(i);
            Row targetRow = targetSheet.createRow(i);
            if (sourceRow != null) {
                for (int j = sourceRow.getFirstCellNum(); j < sourceRow.getLastCellNum(); j++) {
                    Cell sourceCell = sourceRow.getCell(j);
                    Cell targetCell = targetRow.createCell(j);
                    if (sourceCell != null) {
                        switch (sourceCell.getCellType()) {
                            case STRING:
                                targetCell.setCellValue(sourceCell.getStringCellValue());
                                break;
                            case NUMERIC:
                                targetCell.setCellValue(sourceCell.getNumericCellValue());
                                break;
                            case BOOLEAN:
                                targetCell.setCellValue(sourceCell.getBooleanCellValue());
                                break;
                            case BLANK:
                                targetCell.setCellValue("");
                                break;
                            default:
                                break;
                        }
                        // CellStyle sourceCellStyle = sourceCell.getCellStyle();
                        // CellStyle targetCellStyle = targetCell.getSheet().getWorkbook().createCellStyle();
                        // targetCellStyle.cloneStyleFrom(sourceCellStyle);
                        // targetCell.setCellStyle(targetCellStyle);

                        // Copy cell style
                        CellStyle sourceCellStyle = sourceCell.getCellStyle();
                        CellStyle targetCellStyle = styleMap.get(sourceCellStyle);
                        if (targetCellStyle == null) {
                            targetCellStyle = targetSheet.getWorkbook().createCellStyle();
                            targetCellStyle.cloneStyleFrom(sourceCellStyle);
                            styleMap.put(sourceCellStyle, targetCellStyle);
                        }
                        targetCell.setCellStyle(targetCellStyle);
                    }
                }
            }
        }
    }

    private static void copyRows(Sheet sourceSheet, Sheet targetSheet, int startRow, int endRow) {
        Map<CellStyle, CellStyle> styleMap = new HashMap<>();
        for (int i = startRow; i < endRow; i++) {
            Row sourceRow = sourceSheet.getRow(i);
            Row targetRow = targetSheet.createRow(i - startRow + HEADER_ROWS);
            if (sourceRow != null) {
                for (int j = sourceRow.getFirstCellNum(); j < sourceRow.getLastCellNum(); j++) {
                    Cell sourceCell = sourceRow.getCell(j);
                    Cell targetCell = targetRow.createCell(j);
                    if (sourceCell != null) {
                        switch (sourceCell.getCellType()) {
                            case STRING:
                                targetCell.setCellValue(sourceCell.getStringCellValue());
                                break;
                            case NUMERIC:
                                targetCell.setCellValue(sourceCell.getNumericCellValue());
                                break;
                            case BOOLEAN:
                                targetCell.setCellValue(sourceCell.getBooleanCellValue());
                                break;
                            case BLANK:
                                targetCell.setCellValue("");
                                break;
                            default:
                                break;
                        }
                        // CellStyle sourceCellStyle = sourceCell.getCellStyle();
                        // CellStyle targetCellStyle = targetCell.getSheet().getWorkbook().createCellStyle();
                        // targetCellStyle.cloneStyleFrom(sourceCellStyle);
                        // targetCell.setCellStyle(targetCellStyle);

                        // Copy cell style
                        CellStyle sourceCellStyle = sourceCell.getCellStyle();
                        CellStyle targetCellStyle = styleMap.get(sourceCellStyle);
                        if (targetCellStyle == null) {
                            targetCellStyle = targetSheet.getWorkbook().createCellStyle();
                            targetCellStyle.cloneStyleFrom(sourceCellStyle);
                            styleMap.put(sourceCellStyle, targetCellStyle);
                        }
                        targetCell.setCellStyle(targetCellStyle);
                    }
                }
            }
        }
    }

    private static void freezeHeaderRows(Sheet sheet, int headerRows) {
        sheet.createFreezePane(0, headerRows);
    }

    private static void copyMergedRegions(Sheet sourceSheet, Sheet targetSheet) {
        for (int i = 0; i < sourceSheet.getNumMergedRegions(); i++) {
            CellRangeAddress mergedRegion = sourceSheet.getMergedRegion(i);
            targetSheet.addMergedRegion(mergedRegion);
        }
    }

    private static String saveWorkbook(Workbook workbook, String filePath, int partIndex, String descPath) throws IOException {
        File sourceFile = new File(filePath);
        String sourceFileName = sourceFile.getName();
        String fileNamePrefix = sourceFileName.substring(0, sourceFileName.lastIndexOf('.'));
        // String fileName = filePath.substring(0, filePath.lastIndexOf('.'));
        String fileExtension = filePath.substring(filePath.lastIndexOf('.'));
        File file = new File(descPath, fileNamePrefix + "_part" + (partIndex + 1) + fileExtension);
        FileOutputStream outputStream = new FileOutputStream(file);
        workbook.write(outputStream);
        outputStream.close();

        return file.getAbsolutePath();
    }

    public static void main(String[] args) {
        String filePath = "C:\\Users\\99126\\Desktop\\ddd1\\aaaa.xls";
        int maxRows = 1000;
        String descPath = "C:\\Users\\99126\\Desktop\\ddd1\\qqqqqqqq";

        try {
            List<String> paths = splitExcel(filePath, maxRows, descPath);
            System.out.println(paths);
        } catch (IOException e) {
            System.out.println("An error occurred: " + e.getMessage());
            e.printStackTrace();
        }
    }



}
